// db.js
const sqlite3 = require('sqlite3').verbose();
const { open } = require('sqlite');

// Database path
const DATABASE_PATH = '../assets/db/mtalk-database.db';

// Function to open a connection to the SQLite database
async function openDb() {
    return open({
        filename: DATABASE_PATH,
        driver: sqlite3.Database
    });
}

// Create a table if it doesn't exist
async function init() {
    const db = await openDb();
    await db.exec(`
    CREATE TABLE IF NOT EXISTS user (
      id INTEGER PRIMARY KEY AUTOINCREMENT,
      name TEXT
    )
  `);
}

// Insert a new record into the user table
async function insertUser(name) {
    const db = await openDb();
    const result = await db.run('INSERT INTO user (name) VALUES (?)', [name]);
    return result.lastID;
}

// Retrieve all users from the user table
async function getAllUsers() {
    const db = await openDb();
    const users = await db.all('SELECT * FROM user');
    return users;
}

// Update a user's name by ID
async function updateUser(id, name) {
    const db = await openDb();
    const result = await db.run('UPDATE user SET name = ? WHERE id = ?', [name, id]);
    return result.changes;
}

// Delete a user by ID
async function deleteUser(id) {
    const db = await openDb();
    const result = await db.run('DELETE FROM user WHERE id = ?', [id]);
    return result.changes;
}




// Initialize the database (create tables if they don't exist)
init().catch(error => console.error('Database initialization failed:', error));

module.exports = {
    insertUser,
    getAllUsers,
    updateUser,
    deleteUser
};
